package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.FabStageGroup;
import com.scalar.chiptrack.utils.SqlQueryMappings;

/**
 * Created by Veeru.
 * Date: May 17, 2005
 * Time: 6:13:38 PM
 */
public class GroupAssemblyStagesDAO {

    public GroupAssemblyStagesDAO() {
    }

    /** To get the vendor names having the process type as Assembly
     *
     * @return ArrayList
     * @throws com.scalar.ScalarException
     */
    public static ArrayList loadAssemblyVendorLocations( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection        conn      = null;
	    PreparedStatement pstmt     = null;
	    ResultSet         rs        = null;
        ArrayList         locations = null;

	    try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("operations.process.vendorLocation.AssemblyLocations") );
            rs = pstmt.executeQuery();

            locations = new ArrayList();

            while ( rs.next() )
            {
                locations.add( new LabelValueBean( rs.getString("LOCATION_NAME"), rs.getInt("LOCATION_ID")+"" ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException(" Could not load Assembly venodrs in AssemblyProcessDAO", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}

        return locations;
    }

    /** To get all the the Part numbers having the process type as Assembly based on locationId
     *
     * @param locationId
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadAssemblyPartNumbers( DBTransaction dbTransaction, int locationId ) throws ScalarException
    {
        Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
        ArrayList partNumbersList = new ArrayList();

	    try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.process.vendorPart.AssemblyPartNumbers" ) );
            pstmt.setInt( 1, locationId );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                partNumbersList.add( new LabelValueBean( rs.getString("PART_NUMBER"), rs.getString("PROCESSSTEP_ID") ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException(" Could not load Assembly Part Numbers", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}
        return partNumbersList;
    }
    /*
    *   Method to load all the Assembly stages wich are not selected for any groups belong to the given processStepId and locationId
    */
    public static ArrayList loadAssemblyStages( DBTransaction dbTransaction, int processStepId, int locationId ) throws ScalarException {

        Connection        conn  = null;
        ResultSet         rs    = null;
        PreparedStatement pstmt = null;

        ArrayList assemblyStagesList  = new ArrayList();

        try {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT AST_STAGE_NAME, STAGE_ID FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = ? AND STAGE_ID NOT IN ( SELECT STAGE_ID FROM ASSEMBLY_GROUP_TABLE AGT, ASSEMBLY_GROUP_STAGE_TABLE AGST WHERE AGT.PROCESSSTEP_ID = ? AND AGT.LOCATION_ID = ?  AND AGT.GROUP_ID = AGST.GROUP_ID ) AND AST_STAGE_NAME IS NOT NULL ORDER BY AST_STAGE_NAME" );
            pstmt.setInt( 1, processStepId );
            pstmt.setInt( 2, processStepId );
            pstmt.setInt( 3, locationId );
            rs =  pstmt.executeQuery();

            while(rs.next())
            {
                assemblyStagesList.add( new LabelValueBean( rs.getString( "AST_STAGE_NAME"),  rs.getInt( "STAGE_ID")+"" ) );
            }

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Assembly Stages from database", sqle);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return assemblyStagesList;
    }

    /** To get The Assembly Stage Names
     *
     * @param processStepID
     * @param AssemblyStage
     * @return
     * @throws ScalarException
     */
    public static boolean isAssemblyStageExists( DBTransaction dbTransaction, int processStepID, String AssemblyStage ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean found         = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT COUNT(*) AS STAGE_COUNT FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = ? AND AST_STAGE_NAME = ?");
            pstmt.setInt( 1, processStepID );
            pstmt.setString( 2, AssemblyStage );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                found = rset.getInt( "STAGE_COUNT" ) > 0 ? true : false;
            }
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException("Getting Assembly Stage Failed", sql);
        }
        finally
        {
            if( rset != null )
            {
            	try{ rset.close();}catch( Exception e ) {}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch( Exception e ) {}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch( Exception e ) {}
            }
        }
        return found;
    }

    /** Saves The Assembly Stage Name
     *
     * @param dbTransaction
     * @param processStepID
     * @param assemblyStageName
     * @throws ScalarException
     */
    public static void saveAssemblyStage( DBTransaction dbTransaction , int processStepID, String assemblyStageName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean status          = false;

        try
        {
            if (dbTransaction == null)
   			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
            pstmt = conn.prepareStatement("INSERT INTO ASSEMBLY_STAGE_TABLE( PROCESSSTEP_ID, AST_STAGE_NAME, TIMESTAMP ) VALUES ( ?, ?, CURRENT_TIMESTAMP )");

            pstmt.setInt( 1, processStepID );
            pstmt.setString( 2, assemblyStageName );
            pstmt.executeUpdate();
            status = true;
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException(" Saving Assembly Stage Failed", sql);
        }
        finally
        {
            try
                {
                    if (conn != null)
                    {
                        if ( status == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore ) {}

            if(pstmt != null){
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null){
                try{ conn.close(); }catch(Exception e){}
            }
        }
    }

    /**  To Insert the Group name and related Stage names
    *
    * @param fabStage
    * @return boolean
    * @throws ScalarException
    */
     public static boolean saveStageGroups( DBTransaction dbTransaction , FabStage fabStage ) throws ScalarException
     {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        boolean success         = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            boolean remove = false;

            int groupId         = -1;
            int processStepId   =  fabStage.getProcessStepId() ;
            int locationId      =  fabStage.getLocationId();
            int selectedStageId = 0;
            String[] selectedStages = null;
            String groupName        = null;

            ArrayList groupFabStages = fabStage.getFabStages();

            for ( int i = 0; i < groupFabStages.size(); i++ )
            {
                FabStageGroup fabStageGroup = ( FabStageGroup ) groupFabStages.get(i);
                selectedStages = fabStageGroup.getSelectedStages();
                groupName      = fabStageGroup.getGroupName();
                groupId        = fabStageGroup.getGroupId();
                remove         = fabStageGroup.isChecked();
                if ( remove )
                {
                    pstmt = conn.prepareStatement("DELETE FROM ASSEMBLY_GROUP_TABLE WHERE PROCESSSTEP_ID = ? AND LOCATION_ID = ? AND GROUP_ID = ?  ");
                    pstmt.setInt ( 1 , processStepId );
                    pstmt.setInt ( 2 , locationId );
                    pstmt.setInt ( 3 , groupId );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement("DELETE FROM ASSEMBLY_GROUP_STAGE_TABLE WHERE GROUP_ID = ? ");
                    pstmt.setInt ( 1 , groupId );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
                }
                else
                {
                    if ( groupId > 0 )
                    {
                        pstmt = conn.prepareStatement("UPDATE ASSEMBLY_GROUP_TABLE SET AGT_GROUP_NAME = ?, ISDEFAULT = ? WHERE PROCESSSTEP_ID = ? AND LOCATION_ID = ? AND GROUP_ID = ? ");
                        pstmt.setString ( 1 , groupName );
                        pstmt.setInt ( 2, fabStage.getIsDefault() == true ? 0 : 1 );
                        pstmt.setInt ( 3 , processStepId );
                        pstmt.setInt ( 4 , locationId );
                        pstmt.setInt ( 5 , groupId );

                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                        pstmt = conn.prepareStatement("DELETE FROM ASSEMBLY_GROUP_STAGE_TABLE WHERE GROUP_ID = ?");
                        pstmt.setInt ( 1 , groupId );
                        pstmt.executeUpdate();
                        success = true;
                        if ( dbTransaction == null )
                        {
                            if ( conn != null )
                            {
                                if ( success == true )
                                {
                                    conn.commit();
                                }
                                else
                                {
                                    conn.rollback();
                                }
                            }
                        }
                        pstmt.close();
                        pstmt = null;
                    }
                    if ( groupId < 1 )
                    {
                        pstmt = conn.prepareStatement("INSERT INTO ASSEMBLY_GROUP_TABLE ( PROCESSSTEP_ID, LOCATION_ID, AGT_GROUP_NAME, TIMESTAMP, ISDEFAULT ) VALUES ( ?, ?, ?, CURRENT_TIMESTAMP, ? ) ");
                        pstmt.setInt ( 1 , processStepId );
                        pstmt.setInt ( 2 , locationId );
                        pstmt.setString ( 3 , groupName );
                        pstmt.setInt ( 4, fabStage.getIsDefault() == true ? 0 : 1 );
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement( "SELECT @@IDENTITY AS GROUP_ID" );
                        rs = pstmt.executeQuery();
                        if( rs.next() )
                        {
                            groupId = rs.getInt( "GROUP_ID" );
                        }
                        success = true;
                        if ( dbTransaction == null )
                        {
                            if ( conn != null )
                            {
                                if ( success == true )
                                {
                                    conn.commit();
                                }
                                else
                                {
                                    conn.rollback();
                                }
                            }
                        }
                        rs.close();
                        rs = null;
                        pstmt.close();
                        pstmt = null;
                    }
                    if ( selectedStages != null && selectedStages.length > 0 )
                    {
                        pstmt = conn.prepareStatement("INSERT INTO ASSEMBLY_GROUP_STAGE_TABLE( GROUP_ID, STAGE_ID, TIMESTAMP ) VALUES ( ?, ?, CURRENT_TIMESTAMP ) ");
                        for ( int j = 0; j < selectedStages.length; j++)
                        {
                            try
                            {
                                selectedStageId = Integer.parseInt( selectedStages[j] ) ;
                            }
                            catch ( NumberFormatException nfe)
                            {
                                selectedStageId = 0;
                            }

                            if ( selectedStageId > 0 && groupId > 0 )
                            {
                                pstmt.setInt ( 1 , groupId );
                                pstmt.setInt ( 2 , selectedStageId );
                                pstmt.addBatch();
                            }
                        }
                        pstmt.executeBatch();
                    }
                }
            }
            success = true;
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException("Could not save Assembly Stages", sql);
        }

        finally
        {
            try
                {
                    if ( dbTransaction == null )
                    {
                        if ( conn != null )
                        {
                            if ( success == true )
                            {
                                conn.commit();
                            }
                            else
                            {
                                conn.rollback();
                            }
                        }
                    }
                }
                catch ( SQLException ignore ) {}
            if(rs != null){
                try{ rs.close(); }catch(Exception e){}
            }
            if(pstmt != null){
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null){
                try{ conn.close(); }catch(Exception e){}
            }
        }
        return success;
    }
    /**
     * Method to load all the Groups belog to the particular proccesId and locationId
     * @param dbTransaction
     * @param processstepId
     * @param locationId
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadAssemblyStageGroups( DBTransaction dbTransaction, int processstepId , int locationId ) throws ScalarException
    {
        Connection conn             = null;
	    PreparedStatement pstmt     = null;
	    ResultSet rs                = null;
        FabStageGroup fabStageGroup = null;
        ArrayList stageGroupsList   = new ArrayList();

	    try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT GROUP_ID, AGT_GROUP_NAME FROM ASSEMBLY_GROUP_TABLE WHERE PROCESSSTEP_ID = ? AND LOCATION_ID = ?" );
            pstmt.setInt( 1 , processstepId);
            pstmt.setInt( 2 , locationId );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                fabStageGroup = new FabStageGroup();
				fabStageGroup.setGroupId( rs.getInt( "GROUP_ID" ) );
				fabStageGroup.setGroupName( rs.getString( "AGT_GROUP_NAME" ) ) ;
                stageGroupsList.add( fabStageGroup );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException("Could not load Stage Groups", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}
        return stageGroupsList;
   }
   /**
    * Method which loads all the selected stages for Group
    * @param dbTransaction
    * @param groupId
    * @return
    * @throws ScalarException
    */
   public static ArrayList loadSelectedStages( DBTransaction dbTransaction, int groupId ) throws ScalarException
   {
        Connection        conn  = null;
	    PreparedStatement pstmt = null;
	    ResultSet         rs    = null;
        ArrayList selectedStagesList = new ArrayList();

	    try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ("SELECT AGST.STAGE_ID, AST.AST_STAGE_NAME FROM ASSEMBLY_GROUP_STAGE_TABLE AGST, ASSEMBLY_STAGE_TABLE AST WHERE AGST.STAGE_ID = AST.STAGE_ID AND AGST.GROUP_ID = ? ORDER BY AST.AST_STAGE_NAME ");
            pstmt.setInt( 1 , groupId );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                selectedStagesList.add( new org.apache.struts.util.LabelValueBean ( rs.getString( "AST_STAGE_NAME" ) , rs.getInt( "STAGE_ID" )+"" ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException("Could not load selected Stages", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}
        return selectedStagesList;
   }
   /**
    *  Method to convert to Stages Array into Stages List( contains LabelValueBean objects)
    * @param stagesArray
    * @return
    * @throws ScalarException
    */
   public static ArrayList convertPartsArrayToList ( String[] stagesArray ) throws ScalarException
   {
       ArrayList stagesList = new ArrayList();
       try
       {
            for ( int i = 0; i < stagesArray.length; i++)
            {
                int stageId = Integer.parseInt( stagesArray[i] );
                stagesList.add( new LabelValueBean ( getStageName( stageId ) , stageId+"" ) );
            }
       }
       catch( Exception e)
       {
            e.printStackTrace();
       }
       return stagesList;
   }
   /**
    *   Method to get the Stage Name for the given stageId
    */
   public static String getStageName( int stageId ) throws ScalarException
   {
        Connection conn          = null;
        PreparedStatement pstmt  = null;
        ResultSet rs             = null;
        String stageName         = null;

        try
        {
           conn = DataSource.getConnection();

           pstmt   = conn.prepareStatement("SELECT AST_STAGE_NAME FROM ASSEMBLY_STAGE_TABLE WHERE STAGE_ID = ?");
           pstmt.setInt( 1, stageId );
           rs = pstmt.executeQuery();

           while ( rs.next() )
           {
               stageName = rs.getString("AST_STAGE_NAME");
           }
        }
        catch(SQLException sql)
        {
           sql.printStackTrace();
           throw new ScalarException("Exception while loading the Assembly Stage Name", sql);
        }
        finally
        {
           if(rs != null)
           {
               try{ rs.close();}catch(Exception e){}
           }
           if(pstmt != null)
           {
               try{ pstmt.close(); }catch(Exception e){}
           }
           if(conn != null)
           {
               try{ conn.close(); }catch(Exception e){}
           }
        }
        return stageName;
    }

    public static boolean isDefaultGroupExists( DBTransaction dbTransaction, int locationID ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean exists          = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT COUNT(*) AS COUNT FROM ASSEMBLY_GROUP_TABLE WHERE ISDEFAULT = 0 AND LOCATION_ID = ?");
            pstmt.setInt( 1, locationID );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                 exists = rset.getInt( "COUNT" ) > 0 ? true : false;
            }
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException("Exception while checking the existance of default group", sql);
        }
        finally
        {
            if( rset != null )
            {
                try{ rset.close();}catch( Exception e ) {}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch( Exception e ) {}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch( Exception e ) {}
            }
        }
        return exists;
    }

    public static int getDefaultGroupProcessStepId( DBTransaction dbTransaction, int locationID ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        int processStepId       = -1;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT PROCESSSTEP_ID FROM ASSEMBLY_GROUP_TABLE WHERE ISDEFAULT = 0 AND LOCATION_ID = ?");
            pstmt.setInt( 1, locationID );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                 processStepId = rset.getInt("PROCESSSTEP_ID");
            }
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException("Getting Assembly Stage Failed", sql);
        }
        finally
        {
            if( rset != null )
            {
                try{ rset.close();}catch( Exception e ) {}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch( Exception e ) {}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch( Exception e ) {}
            }
        }
        return processStepId;
    }
}
